package Impl;

import dao.IUsNewsDao;
import entity.AdminMan;
import entity.Board;
import entity.UsNews;
import until.DBuntil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UsNewsDaoImpl extends DBuntil implements IUsNewsDao {
    Connection conn = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    @Override
    public int addNews(UsNews usNews) {
        conn=getConn();
        int len=0;
        try {
            String sql="insert into tb_unews(col_title,col_context,col_pic,col_riqi,col_bid,col_aid,col_del)" +
                    "values(?,?,?,now(),?,?,1)";
            pst=conn.prepareStatement(sql);
            pst.setString(1,usNews.getUsNewTitle());
            pst.setString(2,usNews.getUsNewContxt());
            pst.setString(3,usNews.getUsNewPic());
            pst.setInt(4,usNews.getUsNewBid().getBoardId());
            pst.setInt(5,usNews.getUsNewAid().getAdminId());
           len= pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            cloAll(conn,pst,null);
        }
        return len;
    }

    @Override
    public List showNews(int pageSize, int pageNum) {
        List<UsNews> list=new ArrayList<>();
        conn=getConn();
        try {
            String sql="select * from tb_unews order by col_riqi desc limit ?,?";
            pst=conn.prepareStatement(sql);
            pst.setInt(1,pageSize*(pageNum-1));
            pst.setInt(2,pageSize);
            rs=pst.executeQuery();
            while (rs.next()){
                UsNews uw=new UsNews();
                uw.setUsNewId(rs.getInt(1));
                uw.setUsNewTitle(rs.getString(2));
                uw.setUsNewContxt(rs.getString(3));
                uw.setUsNewPic(rs.getString(4));
                uw.setUsNewRiqi(rs.getDate(5));
                uw.setUsNewBid(new Board(rs.getInt(6)));
                uw.setUsNewAid(new AdminMan(rs.getInt(7)));
                uw.setUsNewDel(rs.getInt(8));
                list.add(uw);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            cloAll(conn,pst,rs);
        }
        return list;
    }

    @Override
    public UsNews getNewsId(int newsId) {
        UsNews usNews=null;
        conn=getConn();
        try {
            String sql="SELECT * FROM tb_unews u,tb_board b,tb_admin a WHERE " +
                    "u.col_aid=b.col_bid AND u.col_bid=a.col_id AND u.col_nid=?";
            pst=conn.prepareStatement(sql);
            pst.setInt(1,newsId);
            rs=pst.executeQuery();
           if (rs.next()){
               usNews=new UsNews();
               usNews.setUsNewId(rs.getInt(1));
               usNews.setUsNewTitle(rs.getString(2));
               usNews.setUsNewContxt(rs.getString(3));
               usNews.setUsNewPic(rs.getString(4));
               usNews.setUsNewRiqi(rs.getDate(5));
               usNews.setUsNewBid(new Board(rs.getInt(6)));
               usNews.setUsNewAid(new AdminMan(rs.getInt(7)));
               usNews.setUsNewDel(rs.getInt(8));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            cloAll(conn,pst,rs);
        }
        return usNews;
    }

    @Override
    public int getCount() {
        int count=0;
        conn=getConn();
        try {
            String sql="select count(1) from tb_unews";
            pst=conn.prepareStatement(sql);
            rs= pst.executeQuery();
            if (rs.next()){
                count=rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            cloAll(conn,pst,rs);
        }
        return count;
    }
}
